# -*- coding: utf-8 -*-
# @Time      : 2025-08-01
# @Author    : 2023090007@ahdy.edu.cn
# @File      : loghandler
# @Desc  数据库
import pymysql
from pymysql import Error


class MySQLClient(object):
    def __init__(self, host, user, password, database, port=3306, charset='utf8mb4'):
        """初始化数据库连接参数"""
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset
        self.connection = None
        self.cursor = None

    def connect(self):
        """建立数据库连接"""
        try:
            # 建立连接（pymysql的connect参数与mysql.connector略有不同）
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password.encode('utf-8'),
                database=self.database,
                port=self.port,
                charset=self.charset,
                cursorclass=pymysql.cursors.DictCursor  # 结果以字典形式返回
            )
            
            if self.connection.open:
                self.cursor = self.connection.cursor()
                print(f"成功连接到数据库: {self.database}")
                return True
        except Error as e:
            print(f"连接失败: {str(e)}")
            return False

    def execute_query(self, query, params=None):
        """执行查询语句（SELECT）"""
        try:
            # 检查连接状态，断开则自动重连
            if not self.connection or not self.connection.open:
                if not self.connect():
                    return None
            
            # 执行参数化查询（防止SQL注入）
            self.cursor.execute(query, params or ())
            return self.cursor.fetchall()
        except Error as e:
            print(f"查询错误: {str(e)}")
            return None

    def execute_update(self, query, params=None):
        """执行更新语句（INSERT/UPDATE/DELETE）"""
        try:
            if not self.connection or not self.connection.open:
                if not self.connect():
                    return False
            
            # 执行并提交事务
            affected_rows = self.cursor.execute(query, params or ())
            self.connection.commit()
            return affected_rows  # 返回受影响的行数，便于判断操作结果
        except Error as e:
            print(f"更新错误: {str(e)}")
            self.connection.rollback()  # 出错时回滚
            return False

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection and self.connection.open:
            self.connection.close()
            print("数据库连接已关闭")

# 使用示例
if __name__ == "__main__":
    # 数据库配置（替换为你的实际信息）
    db_config = {
        "host": "localhost",
        "user": "root",
        "password": "root@20250801",
        "database": "dispatch",
        "port": 3306
    }

    # 创建客户端实例
    db = MySQLClient(**db_config)
    
    try:
        # 1. 连接数据库
        if db.connect():
            # 2. 创建表（示例：任务表）
            create_table_sql = """
            CREATE TABLE IF NOT EXISTS tasks (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                data VARCHAR(1024) COMMENT '任务详情(JSON)',
                status INT NOT NULL COMMENT '任务状态',
                host VARCHAR(64) COMMENT '主机IP',
                pid INT COMMENT '进程ID',
                create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
            """
            if db.execute_update(create_table_sql) is not False:
                print("表创建成功或已存在")

            # 3. 插入数据（含时间字段）
            insert_sql = """
            INSERT INTO tasks (data, status, host, pid)
            VALUES (%s, %s, %s, %s)
            """
            task_data = (
                '{"name": "数据备份", "target": "/var/log"}',  # data
                0,  # status: 0-未开始
                '192.168.1.10',  # host
                12345  # pid
            )
            rows = db.execute_update(insert_sql, task_data)
            if rows:
                print(f"插入成功，影响行数: {rows}")

            # 4. 查询数据
            select_sql = "SELECT * FROM tasks WHERE status = %s"
            results = db.execute_query(select_sql, (0,))
            if results:
                print("\n查询结果:")
                for row in results:
                    print(f"ID: {row['id']}, 状态: {row['status']}, 创建时间: {row['create_time']}")

    finally:
        # 5. 关闭连接（无论成功失败都确保关闭）
        db.close()